DROP TABLE HolidayDate;
DROP TABLE Holiday;

GO
CREATE TABLE Holiday
 (
 Id int NOT NULL PRIMARY KEY IDENTITY,
 StateProvinceId int FOREIGN KEY REFERENCES StateProvince(Id),
 Name varchar(255),
 CountryCode varchar(5),
 [Description] varchar(400),
 DisplayOrder int NOT NULL
 );

GO
CREATE NONCLUSTERED INDEX [IX_Holiday_DisplayOrder]
    ON [dbo].Holiday([DisplayOrder] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_Holiday_StateProvinceId]
    ON [dbo].Holiday(StateProvinceId ASC);

GO
CREATE TABLE HolidayDate
 (
 Id int NOT NULL PRIMARY KEY IDENTITY,
 HolidayId int NOT NULL,
 [Date] datetime
 CONSTRAINT [HolidayDate_Holiday] FOREIGN KEY (HolidayId) REFERENCES [dbo].Holiday ([Id]) ON DELETE CASCADE
 );

GO
CREATE NONCLUSTERED INDEX [IX_HolidayDate_HolidayId]
    ON [dbo].HolidayDate(HolidayId ASC);

 GO
CREATE NONCLUSTERED INDEX [IX_HolidayDate_Date]
    ON [dbo].HolidayDate([Date] ASC);